Mini-Project 2

Database Connection

For the purpose of this mini-project I decided to utilize an Azure database rather than include data files, my final project next semester will heavily rely upon connected Azure databases in order to remain updated so this project gave me a good opportunity to practice with this.

The code below creates a Database Connection object which we will be querying throughout this project. You will notice the password here is visible, I am not sure if there is a way around this but for our purposes it doesn’t matter as the user login below is for a read only user so no harm will come of my database if someone decides to connect for their own purposes.

# Azure Database Connection
con <- DBI::dbConnect(odbc::odbc(), 
                      Driver = "SQL Server", 
                      Server = "ncua-dubois.database.windows.net", 
                      Database = "ncua", 
                      uid="readonlylogin",
                      pwd="DBpassword!")

Test Query

As a quick test to ensure the database connection is working the query below will select all credit unions in Florida, along with their most recent asset size, and store the result in the “testQuery” variable.

select icu.CU_NAME as 'CreditUnion', fs.ACCT_010 as 'Assets'
from tbl_Foicu icu
inner join tbl_Fs220 fs on (icu.CU_NUMBER = fs.CU_NUMBER and icu.CYCLE_DATE = fs.CYCLE_DATE)
where 1=1
and icu.CYCLE_DATE = (select max(CYCLE_DATE) from tbl_Foicu)
and icu.STATE='FL'
order by icu.CU_NAME asc

We can see from the head() command below that the query did work successfully.

head(testQuery)

Branch Locations

For the Spatial Analysis portion of this project, the most logical topic to look at is where branches are located, the query below pulls in the locations of all Credit Union branches in the continental US as of the end of every quarter from December 2019 to June 2021. The purpose of this was to look at Covid’s effect on Credit Union Branches, which we will look into deeper later.

select cast(CYCLE_DATE as date) as Date, CU_NAME, Lat as y, Long as x, PhysicalAddressStateCode as State
from tbl_BranchLocations
where 1=1
and PhysicalAddressStateCode not in ('PR','VI','GU','HI','AK')

We can see below that the Branches have already been Geocoded, this was a step I completed outside of R as there are 23,161 unique addresses in this database which would far overwhelm R’s geocoding abilities.

head(cu_branches)

Now that we have the data we need to format it to be plot-able on a map, this is done using the code below.

state_map_data <- map('state', fill = TRUE, plot = FALSE) %>% st_as_sf()

cu_branches_21_sf <- st_as_sf(filter(cu_branches,Date=='2021-06-30'), coords = c('x','y'))
cu_branches_21_sf <- cu_branches_21_sf %>%
  st_set_crs(st_crs(state_map_data))

The ggplot below shows the locations of every Credit Union Branch in relation to the United States.

ggplot() +
  geom_sf(data = state_map_data)+
  geom_sf(data = cu_branches_21_sf, color = 'blue', size = 0.1)+
  labs(title="Credit Union Branches", subtitle="Continental US Only", caption="Source: NCUA (June 2021)")+
  theme(plot.title.position = "plot")

Branch Locations by State

After seeing the map above my initial objective was to get a total count by state, originally I was going to achieve this by doing a simple group_by, and this is why we can see the query includes a column for the branch state. But I changed my mind on this and decided to have R calculate this using the st_intersects function.

The function below calculates the number of points inside each state in the previous map and stores the result in the branch_count attribute in the state sf object.

sf::sf_use_s2(FALSE)
state_map_data$branch_count <- lengths(st_intersects(state_map_data,cu_branches_21_sf))

The plot below using that branch_count attribute to color each state. I originally has plotted the branches on top of this map, similar to the previous map but it felt redundant and cluttered the map. We can see that Texas and California have the most Credit Union Branches which makes sense based on their populations. Perhaps in a future project I will normalize these results by population.

ggplot() +
  geom_sf(data = state_map_data, aes(fill = branch_count))+
  scale_fill_distiller(palette="Greens", direction = +1)+
  #geom_sf(data = cu_branches_21_sf, color = 'black', size = 0.00005)+
  labs(title="Number of Credit Union Branches by State", subtitle="Continental US Only", caption="Source: NCUA (June 2021)",fill='Credit Union Branches')+
  theme(plot.title.position = "plot",legend.position = "bottom")

NA

COVID’s Effect on Branches

As a quick side question I wanted to see how many branches existed prior to COVID compared to now so I graphed the results as a line graph over time below. We can see there was a small dip in the first quarter of 2020 but the number of branches has remained relatively stable. Keep in mind to show the variance I had to play with the Y axis a bit. Less than 2% of branches closed in the first quarter of 2020.

date_branch_counts <- cu_branches %>%
  group_by(Date) %>%
  summarise(count_branches = n())

ggplot(date_branch_counts, aes(x=Date, y=count_branches, group=1))+
  geom_point()+
  geom_line()+
  ylim(20000,22000)+
  theme_minimal()+
  labs(title="Number of CU Branchs", subtitle="Continental US Only", caption="Source: NCUA", y="Count of Branches",x="")+
  theme(plot.title.position = "plot")

Branch Changes in Florida

As a final mapping analysis question for this project I wanted to see the change in Credit Union Branch locations in Florida between December 31st, 2019 and June 30th, 2021. The query below pulls the differences between these two dates and labels if the branch closed, opened or remained existing throughout this time period.

select cast(c.CYCLE_DATE as date) as Date, c.CU_NAME, c.Lat as y, c.Long as x, c.PhysicalAddressStateCode as State,
case when p.SiteId is null then 'New' else 'Existing' end as 'Status'
from tbl_BranchLocations c
left outer join tbl_BranchLocations p on (c.CU_NUMBER = p.CU_NUMBER and c.SiteId = p.SiteId and p.CYCLE_DATE='2019-12-31')
where 1=1
and c.PhysicalAddressStateCode in ('FL')
and c.CYCLE_DATE in ('2021-06-30')

Union

select cast(c.CYCLE_DATE as date) as Date, c.CU_NAME, c.Lat as y, c.Long as x, c.PhysicalAddressStateCode as State,
case when p.SiteId is null then 'Closed' else 'Existing' end as 'Status'
from tbl_BranchLocations c
left outer join tbl_BranchLocations p on (c.CU_NUMBER = p.CU_NUMBER and c.SiteId = p.SiteId and p.CYCLE_DATE='2021-06-30')
where 1=1
and c.PhysicalAddressStateCode in ('FL')
and c.CYCLE_DATE in ('2019-12-31')

order by 2,3,4,1

The code below generates two maps, one for which branches closed and one for which branches opened. I decided to map these seperately do they do not overlap and hide any information.

new_map <- ggplot()+
  geom_sf(data = filter(state_map_data,ID=="florida")) +
  geom_point(data = filter(florida_br_change,Date=='2021-06-30',State=="FL",Status=="Existing"), aes(x = x, y = y), color="Dark Grey", size = 0.1)+
  geom_point(data = filter(florida_br_change,Date=='2021-06-30',State=="FL",Status=="New"), aes(x = x, y = y), color="Dark Green", size = 2)+
  theme_minimal()+
  theme(legend.position="bottom", legend.key.width = unit(1, 'cm'))+
  labs(title=" ", subtitle="Opened Between December 2019 and June 2021",y="",x="",caption="Source: NCUA")+
  theme(plot.title.position = "plot")+
  theme(axis.text.y = element_text(color = "white"))

old_map <- ggplot()+
  geom_sf(data = filter(state_map_data,ID=="florida")) +
  geom_point(data = filter(florida_br_change,Date=='2019-12-31',State=="FL",Status=="Existing"), aes(x = x, y = y), color="Dark Grey", size = 0.1)+
  geom_point(data = filter(florida_br_change,Date=='2019-12-31',State=="FL",Status=="Closed"), aes(x = x, y = y), color="Dark Red", size = 2)+
  theme_minimal()+
  theme(legend.position="bottom", legend.key.width = unit(1, 'cm'))+
  labs(title="Recent Credit Union Branch Openings and Closings", subtitle="Closed Between December 2019 and June 2021",y="",x="",caption=" ")+
  theme(plot.title.position = "plot")
  

The plot below shows the two maps, with the left map showing all credit union branches which closed between these dates and the right map showing all credit union branches which opened. I found these results very interesting, although it doesn’t paint a clear picture, branches are both closing and opening at roughly the same rate. I did notice though, that the Southwest Florida market (the Sarasota, Bradenton, and Port Charlotte areas) has seen 4 openings and no closings. I have known about the potential in this area for a while, so to see this being taken advantage of is encouraging.

grid.arrange(old_map, new_map, ncol=2)

Interactive Plot

For the Interactive plot portion of this Mini-Project I decided to look into the Asset Trends of the top 10 Credit Unions in Florida. I know from past projects that the top 10 Credit Unions are Addition Financial, Campus USA, Eglin, Fairwinds, Grow Financial, GTE, Midflorida, Space Coast, Suncoast, and Vystar.

select icu.CU_NAME as 'CreditUnion'
, cast(fs.ACCT_010 as money) as 'Assets'
, (fs.ACCT_010-st.ACCT_010+0.0)/st.ACCT_010 as 'AssetGrowth'
, cast(fs.CYCLE_DATE as datetime) as 'Cycle_Date'
from tbl_Foicu icu
inner join tbl_Fs220 fs on (icu.CU_NUMBER = fs.CU_NUMBER and icu.CYCLE_DATE = fs.CYCLE_DATE)
left outer join tbl_Fs220 st on (icu.CU_NUMBER = st.CU_NUMBER and st.CYCLE_DATE='2019-12-31')
where 1=1
and icu.STATE='FL'
and icu.cu_number in (68645,68490,67297,68600,68417,9976,196,9788,68391,68702)
order by icu.CU_NAME asc, fs.CYCLE_DATE desc

top_ten_assets$Cycle_Date <- as.Date(top_ten_assets$Cycle_Date)

asset_trend <- ggplot(data = top_ten_assets)+
  geom_line(aes(x = Cycle_Date, y = Assets/1000000000, color=CreditUnion), size=1)+
  geom_line(aes(x = Cycle_Date, y = Assets/1000000000, color=CreditUnion, text=paste("Credit Union: ",CreditUnion,"<br>Date:",Cycle_Date,"<br>Assets: $", format(Assets,big.mark=","))), size=1)+
  scale_y_continuous(labels=scales::dollar_format(),breaks = round(seq(0, 15, by = 1),1))+
  scale_x_date(date_breaks = "2 month", date_labels =  "%b %y")+
  labs(y="Assets ($ Billions)",x="Cycle Date",caption=" ", color="Credit Union",title=" ")+
  theme(plot.title.position = "plot")

Ignoring unknown aesthetics: text

asset_plotly <- ggplotly(asset_trend, tooltip = "text", width = 1000) %>%
  layout(title = list(text = paste0('Asset Trend',
                                    '<br>',
                                    '<sup>',
                                    'Top 10 Florida Credit Unions',
                                    '</sup>')))  
asset_plotly

growth_trend <- ggplot(data = top_ten_assets)+
  geom_line(aes(x = Cycle_Date, y = AssetGrowth, color=CreditUnion), size=1)+
  geom_line(aes(x = Cycle_Date, y = AssetGrowth, color=CreditUnion, text=paste("Credit Union: ",CreditUnion,"<br>Date:",Cycle_Date,"<br>Assets: $", format(Assets,big.mark=","),"<br>Growth: ",round(AssetGrowth*100,1),"%")), size=1)+
  scale_y_continuous(labels=scales::percent_format())+
  scale_x_date(date_breaks = "2 month", date_labels =  "%b %y")+
  labs(y="Assets Growth (%)",x="Cycle Date", color="Credit Union",title=" ")

Ignoring unknown aesthetics: text

  #theme(plot.title.position = "plot")

growth_plotly <- ggplotly(growth_trend, tooltip = "text", width = 1000) %>%
  layout(title = list(text = paste0('Asset Growth Since 2019',
                                    '<br>',
                                    '<sup>',
                                    'Top 10 Florida Credit Unions',
                                    '</sup>')))
growth_plotly